postgresql学习

#postgresql学习| 来源: 网络整理| 查看: 265

在pg里面,只有function 1.删除函数函数名+参数签名 drop FUNCTION if exists HelloWorld2(varchar);

2.CREATE OR REPLACE FUNCTION 不允许你修改一个现有函数的返回类型。 – 要做这些事情，你必须删除并重新创建函数。 –LANGUAGE plpgsql yelowbick只有过程,跟pgsql刚好相反

–入参:anyelement 基本类型表类型游标 –返回:基本类型,记录类型,游标,json,table类型,数组

--块language plpgsql可以注释掉方便用来测试 do --language plpgsql $$ begin raise notice 'ok'; end; $$;

3.行对象row select * from dept; SELECT ROW(‘10’, ‘aaa’, ‘hhhh’)::dept; – 记录变量record类似于行类型变量，但是它们没有预定义的结构， – 只能通过SELECT或FOR命令来获取实际的行结构，因此记录变量在被初始化之前无法访问，否则将引发运行时错误。 –注：RECORD不是真正的数据类型，只是一个占位符。

4.LANGUAGE SQL

-- LANGUAGE SQL 简单的DML语句 create or replace FUNCTION func_get() RETURNS int AS $$ select 1; $$ LANGUAGE SQL; select * from func_get(); create or replace function add_two(int,int) returns int as $$ select $1+$2; $$ LANGUAGE sql; select * from add_two(1,2); --returning返回更新前的值 drop function update_emp(varchar); create or replace function update_emp(varchar) returns varchar as $$ update emp set ename=$1||'ysy' where ename=$1 returning ename; $$ LANGUAGE sql; select * from update_emp('SMITH'); --复合类型 create or replace function sel_emp(emp) returns varchar as $$ select $1.ename; $$ LANGUAGE sql; select sel_emp(e.*) from emp e; --输出类型这里等价于add_two CREATE FUNCTION add_two2 (IN x int, IN y int, OUT sum int) AS $$ SELECT $1 + $2*2; $$ LANGUAGE SQL; CREATE FUNCTION add_two3 (IN x int, IN y int, OUT sum1 int,out sum2 int) AS $$ SELECT $1 + $2*2,$1 + $2; $$ LANGUAGE SQL; select * from add_two3(1,2); --返回集合----------------------------------------- -- 类型为emp,默认之返回第一条记录 CREATE FUNCTION getfoo1() RETURNS emp AS $$ SELECT * FROM emp ; $$ LANGUAGE SQL; select * from getfoo1(); --如果要全部返回setof table CREATE FUNCTION getfoo2() RETURNS setof emp AS $$ SELECT * FROM emp ; $$ LANGUAGE SQL; select * from getfoo2(); --可以访问指定列 select (getfoo2()).ename; drop function getfoo2_1; --可以指定返回表的具体字段类型 create or replace function getfoo2_1() RETURNS emp.ename%type AS $$ SELECT ename FROM emp ; $$ LANGUAGE SQL; select * from getfoo2_1(); --可以返回记录类型 create or replace function getfoo3() RETURNS record AS $$ SELECT empno,ename FROM emp ; $$ LANGUAGE SQL; select getfoo3() ; --如何访问记录类型结果集呢类型要一致，这个声明确实有点不方便 select t.ename from getfoo3() t (empno integer,ename varchar); --自定义返回类型 create type rec_type as (empno int,ename varchar); create or replace function getfoo4() RETURNS rec_type AS $$ SELECT empno,ename FROM emp ; $$ LANGUAGE SQL; --这里type里面指定了,不需要声明 select t.ename from getfoo4() t ; --多态 CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$ SELECT ARRAY[$1, $2]; $$ LANGUAGE SQL; select * from make_array(1,1); --字符要指定类型 select * from make_array('a'::text,'n'::text); -- 如果PL/pgSQL函数的返回类型为多态类型(anyelement或anyarray)，那么函数就会创建一个特殊的参数：$0。 -- 我们仍然可以为该变量设置别名。 create or replace function add_three_values(v1 anyelement, v2 anyelement, v3 anyelement) RETURNS anyelement AS $$ DECLARE result ALIAS FOR $0; BEGIN result := v1 + v2 + v3; RETURN result; --RETURN (v1 + v2 + v3); --等价 END; $$ LANGUAGE plpgsql; select * from add_three_values(1,2,3); --重载 CREATE FUNCTION test(int, real) RETURNS ... CREATE FUNCTION test(int, varchar) RETURNS ...

5.language plpgsql

返回集合类型和json的方式如下:

-- language plpgsql --返回record create or replace function getRecord() returns record as $$ declare v_arr record; begin v_arr := ('aaa'::varchar,'bbb'::varchar); return v_arr; end ; $$ language plpgsql; select t.* from getRecord() t (a varchar,b varchar); --返回数组 create or replace function getArr() returns int[] as $$ declare v_arr int[]; begin v_arr := array [1,2]; return v_arr; end ; $$ language plpgsql; --返回json create or replace function getJson() returns json as $$ declare v_arr json; begin v_arr := '{"name":"李易峰","sex":"男"}'; return v_arr; end ; $$ language plpgsql; --测试 do $$ declare v_rec1 varchar; v_rec2 varchar; v_arr int[]; v_json json; begin --record select t.* into v_rec1,v_rec2 from getRecord() t (a varchar,b varchar); raise notice 'record=,%,%',v_rec1,v_rec2; --arr v_arr:=getArr(); raise notice 'arr=,%',v_arr[1]; v_json:=getJson(); raise notice 'json=,%',v_json->>'name'; end; $$;

6.游标操作

--游标操作 --返回所有结果集 create or replace function getRcord() RETURNS setof record AS $$ declare rec record; --oracle cursor c_emp is ... c_emp cursor for select ename from emp; --pg 不存在c_emp%rowType;这种类型,用record来接受 begin for rec in c_emp loop --将当前行的结果集插入rec return next rec; end loop; return ; end; $$ language plpgsql; --需要声明 select * from getRcord() t (ename varchar); create or replace function getRcord1_2(refcursor) RETURNS setof refcursor AS $$ declare rec record; ref alias for $1; begin open ref for select * from emp; return next ref; end; $$ language plpgsql; select getRcord1_2('a'::refcursor); --返回text 方式1 create or replace function getRcord2() RETURNS text AS $$ declare rec record; v_text text; c_emp cursor for select * from emp; begin v_text:=''; open c_emp; loop fetch c_emp into rec; exit when not FOUND; v_text:=v_text||','||rec.ename; end loop; close c_emp; return v_text; end; $$ language plpgsql; select getRcord2(); --返回text 方式2 drop function getref; create or replace function getRcord3() RETURNS text AS $$ declare ref refcursor; v_rec record; v_text text:=''; begin --open ref for SELECT empno,ename FROM emp ; open ref for execute 'SELECT empno,ename FROM emp' ; --动态游标,只能loop循环,这点跟oracle是一样的 loop fetch ref into v_rec; exit when not found; v_text:=v_text||','||v_rec.ename; end loop; close ref; return v_text; exception when others then raise exception 'error,%',SQLERRM; end; $$ language plpgsql; select * from getRcord3(); --动态游标 /*PL/pgSQL 函数可以向调用者返回游标。这个功能用于从函数里返回多行或多列。要想这么做的时候，该函数打开游标并且把该游标的名字返回给调用者。调用者然后从游标里FETCH行。游标可以由调用者关闭，或者是在事务结束的时候自动关闭。函数返回的游标名可以由调用者声明或者自动生成。要声明一个信使的名字，只要再打开游标之前，给 refcursor 变量赋予一个字串就可以了。 refcursor 变量的字串值将被 OPEN 当作下层的信使的名字使用。不过，如果 refcursor 变量是空，那么 OPEN 将自动生成一个和现有信使不冲突的名字，然后将它赋予 refcursor 变量。注意: 一个绑定的游标变量其名字初始化为对应的字串值，因此信使的名字和游标变量名同名，除非程序员再打开游标之前通过赋值覆盖了这个名字。但是一个未绑定的游标变量初始化的时候缺省是空，因此它会收到一个自动生成的唯一的名字，除非被覆盖。下面的例子显示了一个调用者声明游标名字的方法：*/ create or replace FUNCTION ger_ref(refcursor) RETURNS refcursor AS $$ BEGIN OPEN $1 FOR SELECT ename FROM emp; RETURN $1; END; $$ LANGUAGE plpgsql; --只能用一次那个游标名,用 BEGIN; SELECT ger_ref('funccursor'); FETCH ALL IN funccursor; COMMIT; --下面的例子使用了自动生成的游标名： drop FUNCTION if exists ger_ref2; create or replace FUNCTION ger_ref2() RETURNS refcursor AS $$ DECLARE ref refcursor; BEGIN OPEN ref FOR SELECT ename FROM emp; RETURN ref; END; $$ LANGUAGE plpgsql; BEGIN; SELECT ger_ref2(); FETCH ALL IN ""; COMMIT;

7.函数的调用

--函数的调用 -- yellow brick 是call和execute -- select into 对于返回结果多行的,指挥取一行,如果结果为空,不会报错,跟oracle不一样 drop function into_test; create or replace function into_test() returns text as $$ declare v_name emp.ename%type; begin select e.ename into v_name from emp e; if found then raise notice 'ename=%',v_name; end if; return 'ok'; end; $$ language plpgsql; select into_test(); do $$ declare v_text text; begin --方式0 --v_text:=into_test(); --方式1 --execute 'into_test()'; --方式2 忽略返回值 --perform into_test(); -- 错误select into_test(); end $$;

8.pgsql的批量操作

https://yq.aliyun.com/articles/54786?spm=a2c4e.11153940.0.0.28e2231f3pNJsx

https://yq.aliyun.com/articles/74420?do=login&accounttraceid=b0fcec2d-bb75-4f55-89a2-81fbcfb21110&do=login

【本文地址】

postgresql学习

postgresql学习

今日新闻

推荐新闻